using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Reflection;

using Pampa.Common.Utils;

using Pampa.Grid.Core;
using Pampa.Grid.Core.Filters;
using Pampa.Grid.Exceptions;

using System.Data.SqlClient;

namespace Pampa.GenericEntity
{
    public class GenericEntityDataReader : Pampa.Grid.Data.GridDataReader
    {
        string type;
        string connStringName;
        SqlConnection conn;
        int cursor;
        GenericEntity[] entities;

        SortedList<string, object> parameters;
        MultiSet<string> parameterNames;

        public GenericEntityDataReader()
        {
        }

        public GenericEntityDataReader(string connStringName)
        {
            this.connStringName = connStringName;
        }

        #region Properties

        public string Type
        {
            get { return type; }
            set { type = value; }
        }

        #endregion

        #region Public Functions

        public override void Open(Configuration configuration, int pageSize, int pageNumber)
        {
            OpenConnection();

            int from = pageSize * (pageNumber - 1);
            int length = pageSize;

            this.cursor = -1;

            SqlCommand command = (SqlCommand)GetCommand(configuration);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(from, length, dataTable);

            entities = new GenericEntity[dataTable.Rows.Count];

            for(int i=0; i<dataTable.Rows.Count; i++)
            {
                int entityID = int.Parse(dataTable.Rows[i]["ID"].ToString());
                entities[i] = GenericEntity.Find(entityID);
            }
        }

        public override int GetCount(Configuration configuration)
        {
            OpenConnection();

            IDbCommand command = GetCountCommand(configuration);

            try
            {
                return Int32.Parse(command.ExecuteScalar().ToString());
            }
            catch (DbException ex)
            {
                throw (new DataReaderException("Error counting rows: " + ex.Message));
            }
            finally
            {
                Close();
            }
        }

        public override bool Read()
        {
            this.cursor++;
            return (this.cursor < entities.Length);
        }

        public override object GetValue(string fieldName)
        {
            return entities[this.cursor].GetPropertyValue(fieldName);
        }

        public override void Close()
        {
            this.conn.Close();
            this.cursor = -1;
        }

        #endregion

        #region XML

        public override void LoadXml(System.Xml.XmlNode node)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override void ToXml(ref System.Xml.XmlNode root)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion

        #region Private Functions

        private void OpenConnection()
        {
            if (this.conn == null)
            {
                System.Configuration.ConnectionStringSettings connString = System.Configuration.ConfigurationManager.ConnectionStrings[this.connStringName];

                if (connString == null)
                {
                    throw (new DataReaderException("There is no connection string called '" + this.connStringName + "'"));
                }
                else
                {
                    this.conn = new SqlConnection(connString.ConnectionString);
                }
            }

            this.conn.Open();
        }

        private IDbCommand GetCommand(Configuration configuration)
        {
            parameterNames = new MultiSet<string>();
            parameters = new SortedList<string, object>();

            StringBuilder sb = new StringBuilder();
            sb.Append("select GE.ID from GenericEntity GE \n");

            Dictionary<string, string> joins = new Dictionary<string,string>();
            AppendJoinOrdersBy(configuration, joins, sb);

            sb.Append("where GE.[Type] = '" + this.Type + "'\n");
           
            AppendFilters(configuration, sb);
            AppendOrdersBy(configuration, joins, sb);

            // Construyo el command
            SqlCommand command = new SqlCommand(sb.ToString(), conn);
            foreach (KeyValuePair<string, object> parameter in parameters)
            {
                command.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
            }

            // Test
            // Console.WriteLine(command.CommandText);

            return command;
        }

        private IDbCommand GetCountCommand(Configuration configuration)
        {
            parameterNames = new MultiSet<string>();
            parameters = new SortedList<string, object>();

            StringBuilder sb = new StringBuilder();
            sb.Append("select count(GE.ID) from GenericEntity GE \n");
            sb.Append("where GE.[Type] = '" + this.Type + "'\n");

            AppendFilters(configuration, sb);

            // Construyo el command
            SqlCommand command = new SqlCommand(sb.ToString(), conn);
            foreach (KeyValuePair<string, object> parameter in parameters)
            {
                command.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
            }

            // Test
            // Console.WriteLine(command.CommandText);

            return command;
        }

        private void AppendFilters(Configuration configuration, StringBuilder sb)
        {
            if ((configuration.StaticFilter != null) && (configuration.StaticFilter.InnerFilters.Count>0))
            {
                sb.Append("\n and \n");
                AppendFilterScript(configuration.StaticFilter, sb);
            }

            if ((configuration.MainFilter != null) && (configuration.MainFilter.InnerFilters.Count>0))
            {
                sb.Append("\n and \n");
                AppendFilterScript(configuration.MainFilter, sb);
            }

            if ((configuration.QuickFilter != null) && (configuration.QuickFilter.InnerFilters.Count>0))
            {
                sb.Append("\n and \n");
                AppendFilterScript(configuration.QuickFilter, sb);
            }
        }

        private void AppendJoinOrdersBy(Configuration configuration, Dictionary<string,string> joins, StringBuilder sb)
        {
            int joinsCount = 0;

            foreach(KeyValuePair<string, OrderDirection> orderBy in configuration.OrderBy)
            {
                string joinTableAlias = "GP" + (joinsCount++).ToString();
                joins[orderBy.Key] = joinTableAlias;

                sb.Append(" inner join GenericProperty " + joinTableAlias + " on " + joinTableAlias + ".EntityID = GE.ID and " + joinTableAlias + ".[Name] = '" + orderBy.Key + "' \n");
            }
        }

        private void AppendFilterScript(Filter filter, StringBuilder sb)
        {
            if (filter is GroupFilter)
                AppendFilterScript((GroupFilter)filter, sb);
            else
                AppendFilterScript((SimpleFilter)filter, sb);
        }

        private void AppendFilterScript(GroupFilter groupFilter, StringBuilder sb)
        {
            bool firstFilter = true;

            sb.Append("(\n");

            foreach (Filter childFilter in groupFilter.InnerFilters)
            {
                sb.Append(((!firstFilter) ? (" " + groupFilter.GetFilterType() + " ") : ""));
                AppendFilterScript(childFilter, sb);
                sb.Append("\n");

                firstFilter = false;
            }

            sb.Append(")\n");
        }

        private void AppendFilterScript(SimpleFilter filter, StringBuilder sb)
        {
            string paramName = GetParameterName(filter.Field);

            char propertyType;
            string propertyField;
            object propertyDBValue;

            GenericEntity.GetPropertyDBInfo(filter.ValueScript, out propertyType, out propertyField, out propertyDBValue);

            sb.Append(" exists(select * from GenericProperty GP where EntityID = GE.ID and [Name] = '" + filter.Field + "' and [" + propertyField + "] " + filter.OperationScript + " " + paramName + ")");

            parameters.Add(paramName, propertyDBValue);
        }

        private void AppendOrdersBy(Configuration configuration, Dictionary<string,string> joins,StringBuilder sb)
        {
            bool firstField = true;

            foreach(KeyValuePair<string, OrderDirection> orderBy in configuration.OrderBy)
            {
                string joinTableAlias = joins[orderBy.Key];

                if(firstField)
                    sb.Append(" order by ");
                else
                    sb.Append(",");

                sb.Append(joinTableAlias + ".[DateTimeValue]");
                sb.Append(", " + joinTableAlias + ".[NumericValue]");
                sb.Append(", " + joinTableAlias + ".[BitValue]");
                sb.Append(", " + joinTableAlias + ".[StringValue]");
                
                if(orderBy.Value == OrderDirection.Ascending)
                    sb.Append(" ASC");
                else
                    sb.Append(" DESC");

                firstField = false;
            }
        }

        protected string GetParameterName(string paramName)
        {
            int count = parameterNames.Add(paramName);
            return "@" + paramName + count.ToString();
        }

        #endregion
    }
}